﻿/* 
 * author: www.cnblogs.com/luminji
 * date  : 2011/12/28
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Luminji.Core.Data;
using Luminji.Exam.Infrastructure.Repositories.Subjects;
using Luminji.Exam.Model.Papers;
using Luminji.Exam.Model.PaperTemplates;
using Luminji.Infrastructure.DomainBase;
using Luminji.Infrastructure.UnitOfWork;

namespace Luminji.Exam.Infrastructure.Repositories.Papers
{
    public class PaperRepository : SqlServerRepositoryBase<Paper>, IPaperRepository
    {
        SubjectRepository _sb;

        public PaperRepository(IUnitOfWork unitOfWork)
            : base(unitOfWork)
        {
            _sb = new SubjectRepository(unitOfWork);
        }

        public Paper FindByPaperCode(string paperCode)
        {
            return BuildEntityFromSql(
                "select * from Paper where PaperCode=@paperCode",
                new SqlParameter[] { new SqlParameter("@paperCode", paperCode) });
        }

        public Paper FindWithDetailByPaperCode(string paperCode)
        {
            string sql = @"select *
				from PaperDetail a
                LEFT JOIN dbo.QuestionFace h ON a.QuestionFaceCode = h.QuestionFaceCode
				LEFT JOIN dbo.QuestionUnit b ON a.QuestionUnitCode = b.QuestionUnitCode
            	LEFT JOIN dbo.QuestionUnitBase c ON b.QuestionUnitBaseCode = c.QuestionUnitBaseCode
                LEFT JOIN dbo.Paper d ON a.PaperCode = d.PaperCode
                LEFT JOIN dbo.PaperTemplate e ON d.PaperTemplateCode = e.PaperTemplateCode
				LEFT JOIN dbo.QuestionStem f ON f.QuestionStemCode = a.QuestionStemCode
				LEFT JOIN dbo.PaperTemplatePart g ON b.QuestionUnitCode = g.QuestionUnitCode AND e.PaperTemplateCode=g.PaperTemplateCode
             where a.PaperCode='" + paperCode + @"' 
             ORDER BY a.QuestionNo";
            Paper paper = new Paper { PaperTemplate = new PaperTemplate() };
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                paper.PaperDetails = ds.Tables[0].ToList<PaperDetail>();
            }
            GetPaperChoicOptionsAndSetToPaper(paper.PaperDetails);
            paper.PaperTemplate.PaperTemplateParts = GetPaperTemplatePart(paper.PaperDetails);
            return paper;
        }

        public IList<Paper> FindBySubjectCode(string subjectCode)
        {
            return BuildEntitiesFromSql(
                "select * from Paper where SubjectCode=subjectCode",
                new SqlParameter[] { new SqlParameter("@subjectCode", subjectCode) });
        }

        public IList<Paper> FindBySubjectAndChildrenCodeInPage(string subjectCode, int pageNum)
        {
            if (pageNum < 1)
            {
                pageNum = 1;
            }
            string sqlWhere = InitWhere(subjectCode);
            string sql = string.Format(
                @"
                WITH x AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY PaperName DESC) AS 'RowNumber', * FROM Paper
                WHERE SubjectCode IN ({0})
                )
                SELECT * FROM x where RowNumber between {1} and {2}", sqlWhere, 10 * (pageNum - 1) + 1, 10 * pageNum);
            return BuildEntitiesFromSql(sql);
        }

        public IList<Paper> FindBySubjectAndChildrenCode(string subjectCode)
        {
            string sqlWhere = InitWhere(subjectCode);
            return BuildEntitiesFromSql("select * from Paper where SubjectCode in (" + sqlWhere + ")");
        }

        public int FindPapersNumsBySubjectAndChildrenCode(string subjectCode)
        {
            string sqlWhere = InitWhere(subjectCode);
            string sql = string.Format(
                @"SELECT count(*) FROM Paper WHERE SubjectCode IN ({0})", sqlWhere);
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                int i = (int)ds.Tables[0].Rows[0][0];
                return i / 10 + 1;
            }
        }

        public IList<Paper> Find(string userCode, int showType)
        {
            throw new NotImplementedException();
        }

        private string InitWhere(string subjectCode)
        {
            var subjects = _sb.Find();
            var subjectCodes = from p in subjects
                               where p.SubjectCode == subjectCode || p.ParentCode == subjectCode
                               select p.SubjectCode;
            string sqlWhere = subjectCodes.Aggregate(string.Empty, (current, code) => current + string.Format(@"'{0}',", code));
            return sqlWhere.Substring(0, sqlWhere.Length - 1);
        }

        private void GetPaperChoicOptionsAndSetToPaper(IList<PaperDetail> details)
        {
            string strWhere = details.Aggregate(string.Empty, (current, paperDetail) => current + ("'" + paperDetail.QuestionStemCode + "',"));
            string sql = @"select * from dbo.QuestionOptions where QuestionStemCode in (" +
                         strWhere.Substring(0, strWhere.Length - 1) + ")";
            IList<QuestionOption> options = null;
            using (DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                options = ds.Tables[0].ToList<QuestionOption>();
            }
            foreach (PaperDetail paperDetail in details)
            {
                var currentDetailOprations = from p in options where p.QuestionStemCode == paperDetail.QuestionStemCode select p;
                foreach (QuestionOption currentDetailOpration in currentDetailOprations)
                {
                    paperDetail.QuestionOptions.Add(currentDetailOpration);
                }
            }
        }

        IEnumerable<PaperTemplatePart> GetPaperTemplatePart(IList<PaperDetail> details)
        {
            IEnumerable<PaperTemplatePart> parts = null;
            parts = (from p in details
                     select new PaperTemplatePart()
                     {
                         PaperTemplatePartCode = p.PaperTemplatePartCode,
                         PaperTemplatePartName = p.PaperTemplatePartName
                     }).Distinct();
            return parts;
        }

        public override IList<Paper> Find()
        {
            throw new NotImplementedException();
        }

        public override Paper Find(object key)
        {
            throw new NotImplementedException();
        }

        public override void Add(Paper item)
        {

        }

        public override void Remove(Paper item)
        {
            throw new NotImplementedException();
        }
    }
}
